using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Fodee.DataEntity;

namespace Fodee.CodeGenerator
{

    public partial class DataAccessParameterGenerator : Form
    {
        public DataAccessParameterGenerator()
        {
            InitializeComponent();
        }

        private void DataAccessParameterGenerator_Load(object sender, EventArgs e)
        {
            FodeeDataSet ds = new FodeeDataSet();

            if (ds.Tables.Count == 0)
                return;
            
            List<ListItem> liTables = new List<ListItem>();

            foreach (DataTable dt in ds.Tables)
            {
                liTables.Add(new ListItem(dt.TableName, dt.TableName));
            }

            this.cbxTableName.DataSource = liTables;
            this.cbxTableName.DisplayMember = "Text";
            this.cbxTableName.ValueMember = "Value";
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string tableName = this.cbxTableName.SelectedValue.ToString();
            if (string.IsNullOrEmpty(tableName))
                return;

            string result = GenerateHeader(tableName)
                 + GenerateInsert(tableName)
                 + GenerateAdd(tableName)
                 + GenerateUpdate(tableName)
                 + GenerateDelete(tableName)
                 + GenerateGetByID(tableName)
                 + GenerateGetAll(tableName)
                 + GenerateFooter();

            this.rtbxResult.Text = result;


        }

        private void button4_Click(object sender, EventArgs e)
        {
            string tableName = this.cbxTableName.SelectedValue.ToString();
            if (string.IsNullOrEmpty(tableName))
                return;

            string result = GenerateBCHeader(tableName)
               + GenerateBCInsert(tableName)
               + GenerateBCAdd(tableName)
               + GenerateBCUpdate(tableName)
               + GenerateBCDelete(tableName)
               + GenerateBCGetByID(tableName)
               + GenerateBCGetAll(tableName)
               + GenerateFooter();

            this.rtbxResult.Text = result;
        }

        #region Generate DA

        private void button2_Click(object sender, EventArgs e)
        {
            //SaveFileDialog file = new SaveFileDialog();
            //file.Filter = "*.cs|*.cs";
            //file.FilterIndex = 1;

            FodeeDataSet ds = new FodeeDataSet();

            if (ds.Tables.Count == 0)
                return;

            List<ListItem> liTables = new List<ListItem>();

            foreach (DataTable dt in ds.Tables)
            {
                string tableName = dt.TableName;
                if (!dt.TableName.Contains("fod_"))
                {
                    continue;
                }
                //liTables.Add(new ListItem(dt.TableName, dt.TableName));
                string result = GenerateHeader(tableName)
                + GenerateInsert(tableName)
                + GenerateAdd(tableName)
                + GenerateUpdate(tableName)
                + GenerateDelete(tableName)
                + GenerateGetByID(tableName)
                + GenerateGetAll(tableName)
                + GenerateFooter();

                tableName = tableName.Replace("_", "");//.Remove(3, 1);
                System.IO.StreamWriter sw = new System.IO.StreamWriter("c:\\Files\\" + tableName + "DA.cs", false);
                try
                {
                    sw.WriteLine(result);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    sw.Flush();
                    sw.Close();
                }
            }
        }

        private string GenerateHeader(string tableName)
        {
            tableName = tableName.Replace("_","");//.Remove(3, 1);
            StringBuilder str = new StringBuilder();
            str.Append("using System;");
            str.Append("\n");
            str.Append("using System.Collections.Generic;");
            str.Append("\n");
            str.Append("using System.Text;");
            str.Append("\n");
            str.Append("using Fodee.DataEntity;");
            str.Append("\n");
            str.Append("using System.Data.Common;");
            str.Append("\n");
            str.Append("using System.Data;");
            str.Append("\n");
            str.Append("\n");
            str.Append("namespace Fodee.DataAccess");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("public class " + tableName + "DA : FodeeDABase");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("public " + tableName + "DA()");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("\n");
            return str.ToString();
        }

        private string GenerateInsert(string tableName)
        {
            StringBuilder str = new StringBuilder();
            str.Append("public bool Insert(FodeeDataSet ds, DbTransaction dbTransaction)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("DbCommand command = Db.GetStoredProcCommand(\"P_" + tableName + "_I\");");
            str.Append("\n");

            FodeeDataSet ds = new FodeeDataSet();
            foreach (DataTable dt in ds.Tables)
            {
                if (dt.TableName == tableName)
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        if (col.ColumnName.ToLower() == "id")
                        {
                            str.Append("Db.AddOutParameter(command, \"@ID\", System.Data.DbType.Int32, 8);");
                        }
                        else
                        {

                            string type = TypeToDbType(col.DataType).ToString();
                            str.Append("Db.AddInParameter(command, \"@" + col.ColumnName + "\", System.Data.DbType." + type + ", \"" + col.ColumnName + "\", DataRowVersion.Current);");
                        }
                        str.Append("\n");
                    }
                }
            }
            str.Append("int result = Db.UpdateDataSet(ds, ds." + tableName + ".TableName, command, null, null, dbTransaction);");
            str.Append("\n");
            str.Append(" if (result > 0)");
            str.Append("\n");
            str.Append("return true;");
            str.Append("\n");
            str.Append("return false;");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("\n");
            return str.ToString();
        }

        private string GenerateAdd(string tableName)
        {
            StringBuilder str = new StringBuilder();
            str.Append("public int Add(FodeeDataSet ds, DbTransaction dbTransaction)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("DbCommand command = Db.GetStoredProcCommand(\"P_" + tableName + "_I\");");
            str.Append("\n");

            FodeeDataSet ds = new FodeeDataSet();
            foreach (DataTable dt in ds.Tables)
            {
                if (dt.TableName == tableName)
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        if (col.ColumnName.ToLower() == "id")
                        {
                            str.Append("Db.AddOutParameter(command, \"@ID\", System.Data.DbType.Int32, 8);");
                        }
                        else
                        {

                            string type = TypeToDbType(col.DataType).ToString();
                            str.Append("Db.AddInParameter(command, \"@" + col.ColumnName + "\", System.Data.DbType." + type + ", \"" + col.ColumnName + "\", DataRowVersion.Current);");
                        }
                        str.Append("\n");
                    }
                }
            }
            str.Append("Db.UpdateDataSet(ds, ds." + tableName + ".TableName, command, null, null, dbTransaction);");
            str.Append("\n");
            str.Append("int result = int.Parse(Db.GetParameterValue(command, \"@ID\").ToString());");
            str.Append("\n");
            str.Append("return result;");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("\n");
            return str.ToString();
        }

        private string GenerateUpdate(string tableName)
        {
            StringBuilder str = new StringBuilder();
            str.Append("public bool Update(FodeeDataSet ds, DbTransaction dbTransaction)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("DbCommand command = Db.GetStoredProcCommand(\"P_" + tableName + "_U\");");
            str.Append("\n");

            FodeeDataSet ds = new FodeeDataSet();
            foreach (DataTable dt in ds.Tables)
            {
                if (dt.TableName == tableName)
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        string type = TypeToDbType(col.DataType).ToString();
                        str.Append("Db.AddInParameter(command, \"@" + col.ColumnName + "\", System.Data.DbType." + type + ", \"" + col.ColumnName + "\", DataRowVersion.Current);");
                        str.Append("\n");
                    }
                }
            }
            str.Append("int result = Db.UpdateDataSet(ds, ds." + tableName + ".TableName, null, command, null, dbTransaction);");
            str.Append("\n");
            str.Append(" if (result > 0)");
            str.Append("\n");
            str.Append("return true;");
            str.Append("\n");
            str.Append("return false;");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("\n");
            return str.ToString();
        }

        private string GenerateDelete(string tableName)
        {
            StringBuilder str = new StringBuilder();
            str.Append("public bool Delete(int id, DbTransaction dbTransaction)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("DbCommand command = Db.GetStoredProcCommand(\"P_" + tableName + "_D\");");
            str.Append("\n");
            str.Append("Db.AddInParameter(command, \"@ID\", System.Data.DbType.Int32, id);");
            str.Append("\n");

            str.Append("int result = Db.ExecuteNonQuery(command, dbTransaction);");
            str.Append("\n");
            str.Append(" if (result > 0)");
            str.Append("\n");
            str.Append("return true;");
            str.Append("\n");
            str.Append("return false;");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("\n");
            return str.ToString();
        }

        private string GenerateGetByID(string tableName)
        {

            StringBuilder str = new StringBuilder();
            str.Append(" public FodeeDataSet GetByID(int id)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("FodeeDataSet ds = new FodeeDataSet();"); 
            str.Append("\n");
            str.Append("DbCommand command = Db.GetStoredProcCommand(\"P_" + tableName + "_S\");");
            str.Append("\n");
            str.Append("Db.AddInParameter(command, \"@ID\", System.Data.DbType.Int32, id);");
            str.Append("\n");
            
            str.Append("Db.LoadDataSet(command, ds,ds." + tableName + ".TableName);");
            str.Append("\n");
            str.Append("return ds;");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("\n");
            return str.ToString();
        }

        private string GenerateGetAll(string tableName)
        {

            StringBuilder str = new StringBuilder();
            str.Append(" public FodeeDataSet GetAll()");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("FodeeDataSet ds = new FodeeDataSet();");
            str.Append("\n");
            str.Append("DbCommand command = Db.GetStoredProcCommand(\"P_" + tableName + "_A\");");
            str.Append("\n");

            str.Append("Db.LoadDataSet(command, ds,ds." + tableName + ".TableName);");
            str.Append("\n");
            str.Append("return ds;");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("\n");
            return str.ToString();
        }

        #endregion

        private string GenerateFooter()
        {
            StringBuilder str = new StringBuilder();
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("\n");
            return str.ToString();
        }

        private string GenerateBeginTryCatch()
        {
            StringBuilder str = new StringBuilder();
           
            str.Append("try");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");

            return str.ToString();
        }

        private string GenerateEndTryCatch()
        {
            StringBuilder str = new StringBuilder();
      
            str.Append("}");
            str.Append("\n");
            str.Append("catch (Exception ex)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("//TODO:handle exception");
            str.Append("\n");
            str.Append("//TODO:log exception");
            str.Append("\n");
            str.Append("throw ex;");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");

            return str.ToString();
        }

        public static DbType TypeToDbType(Type t)
        {
            DbType dbt;
            try
            {
                dbt = (DbType)Enum.Parse(typeof(DbType), t.Name);
            }
            catch
            {
                dbt = DbType.Object;
            }
            return dbt;
        }

        #region Generate BC

        private void button3_Click(object sender, EventArgs e)
        {

            FodeeDataSet ds = new FodeeDataSet();

            if (ds.Tables.Count == 0)
                return;

            List<ListItem> liTables = new List<ListItem>();

            foreach (DataTable dt in ds.Tables)
            {
                string tableName = dt.TableName;
                if (!dt.TableName.Contains("fod_"))
                {
                    continue;
                }
                //liTables.Add(new ListItem(dt.TableName, dt.TableName));
                string result = GenerateBCHeader(tableName)
                + GenerateBCInsert(tableName)
                + GenerateBCAdd(tableName)
                + GenerateBCUpdate(tableName)
                + GenerateBCDelete(tableName)
                + GenerateBCGetByID(tableName)
                + GenerateBCGetAll(tableName)
                + GenerateFooter();

                tableName = tableName.Replace("_", "");//.Remove(3, 1);
                System.IO.StreamWriter sw = new System.IO.StreamWriter("c:\\Files\\" + tableName + "BC.cs", false);
                try
                {
                    sw.WriteLine(result);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    sw.Flush();
                    sw.Close();
                }
            }
        }

        private string GenerateBCHeader(string tableName)
        {
            tableName = tableName.Replace("_", "");//.Remove(3, 1);
            StringBuilder str = new StringBuilder();
            str.Append("using System;");
            str.Append("\n");
            str.Append("using System.Collections.Generic;");
            str.Append("\n");
            str.Append("using System.Text;");
            str.Append("\n");
            str.Append("using Fodee.DataEntity;");
            str.Append("\n");
            str.Append("using System.Data.Common;");
            str.Append("\n");
            str.Append("using Fodee.DataAccess;");
            str.Append("\n");
            str.Append("\n");
            str.Append("namespace Fodee.BusinessComponent");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("public class " + tableName + "BC : FodeeBCBase");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("public readonly " + tableName + "DA da = new " + tableName + "DA();");
            str.Append("\n");
            str.Append("public " + tableName + "BC()");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("\n");
            return str.ToString();
        }

        private string GenerateBCInsert(string tableName)
        {
            StringBuilder str = new StringBuilder();
            str.Append("public bool Insert(FodeeDataSet ds)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            //str.Append("try");
            //str.Append("\n");
            //str.Append("{");
            str.Append(GenerateBeginTryCatch());
            str.Append("\n");
            str.Append("using (DbTransaction dbTransaction = da.GetConnection().BeginTransaction())");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("try");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("bool result = da.Insert(ds, dbTransaction);");
            str.Append("\n");
            str.Append("if (result)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("dbTransaction.Commit();");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("else");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("dbTransaction.Rollback();");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("return result;");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("catch (Exception ex)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("dbTransaction.Rollback();");
            str.Append("\n");
            str.Append("throw ex;");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            //str.Append("}");
            //str.Append("\n");
            //str.Append("catch (Exception ex)");
            //str.Append("\n");
            //str.Append("{");
            //str.Append("\n");
            //str.Append("//TODO:handle exception");
            //str.Append("\n");
            //str.Append("//TODO:log exception");
            //str.Append("\n");
            //str.Append("throw ex;");
            //str.Append("\n");
            //str.Append("}");
            str.Append(GenerateEndTryCatch());
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("\n");
            return str.ToString();
        }

        private string GenerateBCAdd(string tableName)
        {
            StringBuilder str = new StringBuilder();
            str.Append("public int Add(FodeeDataSet ds)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            //str.Append("try");
            //str.Append("\n");
            //str.Append("{");
            str.Append(GenerateBeginTryCatch());
            str.Append("\n");
            str.Append("using (DbTransaction dbTransaction = da.GetConnection().BeginTransaction())");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("try");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("int result = da.Add(ds, dbTransaction);");
            str.Append("\n");
            str.Append("dbTransaction.Commit();");
            str.Append("\n");
            str.Append("return result;");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("catch (Exception ex)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("dbTransaction.Rollback();");
            str.Append("\n");
            str.Append("throw ex;");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            //str.Append("}");
            //str.Append("\n");
            //str.Append("catch (Exception ex)");
            //str.Append("\n");
            //str.Append("{");
            //str.Append("\n");
            //str.Append("//TODO:handle exception");
            //str.Append("\n");
            //str.Append("//TODO:log exception");
            //str.Append("\n");
            //str.Append("throw ex;");
            //str.Append("\n");
            //str.Append("}");
            str.Append(GenerateEndTryCatch());
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("\n");
            return str.ToString();
        }

        private string GenerateBCUpdate(string tableName)
        {
            StringBuilder str = new StringBuilder();
            str.Append("public bool Update(FodeeDataSet ds)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            //str.Append("try");
            //str.Append("\n");
            //str.Append("{");
            str.Append(GenerateBeginTryCatch());
            str.Append("\n");
            str.Append("using (DbTransaction dbTransaction = da.GetConnection().BeginTransaction())");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("try");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("bool result = da.Update(ds, dbTransaction);");
            str.Append("\n");
            str.Append("if (result)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("dbTransaction.Commit();");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("else");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("dbTransaction.Rollback();");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("return result;");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("catch (Exception ex)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("dbTransaction.Rollback();");
            str.Append("\n");
            str.Append("throw ex;");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            //str.Append("}");
            //str.Append("\n");
            //str.Append("catch (Exception ex)");
            //str.Append("\n");
            //str.Append("{");
            //str.Append("\n");
            //str.Append("//TODO:handle exception");
            //str.Append("\n");
            //str.Append("//TODO:log exception");
            //str.Append("\n");
            //str.Append("throw ex;");
            //str.Append("\n");
            //str.Append("}");
            str.Append(GenerateEndTryCatch());
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("\n");
            return str.ToString();
        }

        private string GenerateBCDelete(string tableName)
        {
            StringBuilder str = new StringBuilder();
            str.Append("public bool Delete(int id)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            //str.Append("try");
            //str.Append("\n");
            //str.Append("{");
            str.Append(GenerateBeginTryCatch());
            str.Append("\n");
            str.Append("using (DbTransaction dbTransaction = da.GetConnection().BeginTransaction())");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("try");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("bool result = da.Delete(id, dbTransaction);");
            str.Append("\n");
            str.Append("if (result)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("dbTransaction.Commit();");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("else");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("dbTransaction.Rollback();");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("return result;");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("catch (Exception ex)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append("dbTransaction.Rollback();");
            str.Append("\n");
            str.Append("throw ex;");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append(GenerateEndTryCatch());
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("\n");
            return str.ToString();
        }

        private string GenerateBCGetByID(string tableName)
        {
            StringBuilder str = new StringBuilder();
            str.Append("public FodeeDataSet GetByID(int id)");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append(GenerateBeginTryCatch());
            str.Append("\n");
            str.Append("return da.GetByID(id);");
            str.Append("\n");
            str.Append(GenerateEndTryCatch());
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("\n");
            return str.ToString();
        }

        private string GenerateBCGetAll(string tableName)
        {
            StringBuilder str = new StringBuilder();
            str.Append("public FodeeDataSet GetAll()");
            str.Append("\n");
            str.Append("{");
            str.Append("\n");
            str.Append(GenerateBeginTryCatch());
            str.Append("\n");
            str.Append("return da.GetAll();");
            str.Append("\n");
            str.Append(GenerateEndTryCatch());
            str.Append("\n");
            str.Append("}");
            str.Append("\n");
            str.Append("\n");
            return str.ToString();
        }

        #endregion

        
    }

    public partial class ListItem
    {
        private string _text;
        public string Text
        {
            get
            {
                return _text;
            }
            set
            {
                _text = value;
            }
        }

        private string _value;
        public string Value
        {
            get
            {
                return _value;
            }
            set
            {
                _value = value;
            }
        }

        public ListItem(string text, string value)
        {
            this.Text = text;
            this.Value = value;
        }
    }
}